<?php
class DBManager{

	private $db;

	public function DBManager(Database $db){
		$this->db = $db;
	}

	public function createSQLCreateTableDump($schema = ""){
		$queries = array();
		$tQueries = array();
		$tReferences = array();

		$tree = array();

		if(null !== $this->db){

			try{
				$query = "SHOW TABLES;";
				$res = $this->db->prepare($query)->fetchObjectAll();
	
				if($res){
					foreach( $res as $table){
						$tableName = current(get_object_vars($table));
	
						$query = $this->createSQLCreateTableQuery($tableName);
						$references = $this->createSQLCreateConstraintDump($tableName);
	
						$tQueries[$tableName] = $query;
	
						if(count($references) > 0){
							$tReferences[$tableName] = $references;
						}
					}
				}
	
				// These tables have no reference to another table, thus these can be safely created first
				$queries = array_diff_key($tQueries, $tReferences);
	
				// these tables do have a reference
				$intersect = array_intersect_key($tQueries, $tReferences);
	
				// for first run only
				$firstKeys = array_keys($queries);
	
				$count = count($tQueries)+1;
				$maxcount = 0;
	
				// now keep walking the $tReferences array untill it is empty
				while(count($tReferences) > 0 && ($maxcount++ < $count)){
	
					//reset the keys to be removed from $tReferences
					$currentKeys = (null === $firstKeys) ? $newKeys : $firstKeys;
					$firstKeys = null;
					$newKeys = array();
	
					// walk through all remaining $tRferences
					// $reference has to be altered, thus pass by reference
					foreach($tReferences as $rkey => &$reference){
	
						//walk through all the current (new) keys that were added in the previous run
						foreach($currentKeys as $key){
	
							//if a key is found in the reference list, remove it
							if(false !== ($index = array_search($key, $reference))){
								unset($reference[$index]);
							}
						}
	
						// if this table has no more references
						if(count($reference) === 0){
	
							$queries[$rkey] = $tQueries[$rkey];
	
							$newKeys[] = $rkey;
	
	
						}
					}
	
					if(count($newKeys) === 0){
						die("none were removed, thus none were added, thus infinite loop");
					}
	
					// remove the tables in the reference array because we don't need to loop these anymore
					foreach($newKeys as $key){
						unset($tReferences[$key]);
					}
				}
			}catch(Exception $e){
				
			}
		}

		$str = "";

		if(null !== $this->db){
			$str .= "\n\n\"DROP SCHEMA IF EXISTS `{$schema}`;\",";
		}
		
		$str .= "\n\"CREATE SCHEMA IF NOT EXISTS `{$schema}` DEFAULT CHARACTER SET utf8;\",";
		$str .= "\n\"USE `{$schema}`;\",";

		foreach($queries as $query){
			$str .= "\n\n\"{$query};\",";
		}

		$php = "<?php\n\nreturn array({$str}\n\n);";

		return $php;
	}

	private function createSQLCreateTableQuery($tableName){
		$query = "SHOW CREATE TABLE {$tableName}";
		$res = $this->db->prepare($query)->fetchObjectOnce();

		return $res->{"Create Table"};
	}

	private function createSQLCreateConstraintDump($tableName){
		$query =
"SELECT b.referenced_table_name
FROM information_schema.table_constraints a
JOIN information_schema.key_column_usage b
ON a.table_schema = b.table_schema AND a.constraint_name = b.constraint_name
WHERE a.table_schema=database() AND a.constraint_type='FOREIGN KEY' AND b.table_name = '{$tableName}'
ORDER BY b.table_name, b.constraint_name;";

		$res = $this->db->prepare($query)->fetchObjectAll();

		$references = array();

		if($res){
			foreach($res as $row){
				$referencedTableName = $row->{"referenced_table_name"};

				$references[] = $referencedTableName;
			}
		}

		return $references;
	}
}